using System;
using System.Collections;
using System.Collections.Specialized;
using System.Data;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using System.Text;
using System.Collections.Generic;
using eShop.Model;
using eShop.Utility;

namespace eShop.DataAccess
{
    public partial class PartnerProductDAO : DatabaseUtility
    {
        public static Decimal CreateNewTblPartnerProduct
        (
            Int32 partnerId,
            Int32 productId,
            Decimal sellPrice,
            Decimal productionCost
        )
        {
            //creating an empty TblPartnerProduct object
            ManufacturerProduct tblPartnerProduct = new ManufacturerProduct();
            tblPartnerProduct.PartnerId = partnerId;
            tblPartnerProduct.ProductId = productId;
            tblPartnerProduct.SellPrice = sellPrice;
            tblPartnerProduct.ProductionCost = productionCost;

            return PartnerProductDAO.CreateNewTblPartnerProduct(tblPartnerProduct);
        }

        public static bool UpdateTblPartnerProduct
        (
            Decimal partnerProductId,
            Int32 partnerId,
            Int32 productId,
            Decimal sellPrice,
            Decimal productionCost
        )
        {
            ManufacturerProduct tblPartnerProduct = PartnerProductDAO.GetTblPartnerProductByPartnerProductId(partnerProductId);

            tblPartnerProduct.PartnerId = partnerId;
            tblPartnerProduct.ProductId = productId;
            tblPartnerProduct.SellPrice = sellPrice;
            tblPartnerProduct.ProductionCost = productionCost;

            return PartnerProductDAO.UpdateTblPartnerProduct(tblPartnerProduct);
        }

        public static bool DeleteTblPartnerProduct
        (
            Decimal partnerProductId
        )
        {
            //Validate Input
            if (partnerProductId <= GetPartnerProductIdMinValue)
                throw (new ArgumentOutOfRangeException("partnerProductId"));

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            AddParameterToSqlCmd(sqlCmd, "@PartnerProductId", SqlDbType.Decimal, 9, ParameterDirection.Input, partnerProductId);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_TBLPARTNERPRODUCT_DELETETBLPARTNERPRODUCT);
            ExecuteScalarCmd(sqlCmd);
            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return (returnValue == 0 ? false : true);

        }

        public static bool DeleteTblPartnerProductList(System.Collections.Generic.List<Decimal> partnerProductIdListToDelete)
        {
            //Validate Input
            foreach (Decimal partnerProductId in partnerProductIdListToDelete)
                if (partnerProductId <= GetPartnerProductIdMinValue)
                    throw (new ArgumentOutOfRangeException("partnerProductId"));

            //performing deletion operation //

            string xmlString = FormatXMLForIdArray(partnerProductIdListToDelete);

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            AddParameterToSqlCmd(sqlCmd, "@PartnerProductIdList", SqlDbType.Xml, xmlString.ToString().Length, ParameterDirection.Input, xmlString.ToString());
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_TBLPARTNERPRODUCT_DELETETBLPARTNERPRODUCTLIST);
            ExecuteScalarCmd(sqlCmd);
            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return (returnValue == partnerProductIdListToDelete.Count ? true : false);
        }

        public static ManufacturerProduct GetTblPartnerProductByPartnerProductId(Decimal partnerProductId)
        {
            if (partnerProductId <= GetPartnerProductIdMinValue)
                throw (new ArgumentOutOfRangeException("partnerProductId"));

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@PartnerProductId", SqlDbType.Decimal, 9, ParameterDirection.Input, partnerProductId);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_TBLPARTNERPRODUCT_GETTBLPARTNERPRODUCTBYPARTNERPRODUCTID);
            GenerateCollectionFromReader test = new GenerateCollectionFromReader(GenerateTblPartnerProductCollectionFromReader);
            List<ManufacturerProduct> objCollection = ((List<ManufacturerProduct>)ExecuteReaderCmd(sqlCmd, test));

            if (objCollection.Count > 0)
                return objCollection[0];
            else
                return null;
        }

        public static List<ManufacturerProduct> GetAllTblPartnerProduct()
        {

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_TBLPARTNERPRODUCT_GETALLTBLPARTNERPRODUCT);
            GenerateCollectionFromReader test = new GenerateCollectionFromReader(GenerateTblPartnerProductCollectionFromReader);
            List<ManufacturerProduct> objCollection = ((List<ManufacturerProduct>)ExecuteReaderCmd(sqlCmd, test));

            return objCollection;
        }

        public static List<ManufacturerProduct> GetAllTblPartnerProductPaged(String orderBy, Int32 startRowIndex, Int32 maximumRows)
        {
            //Validate Input
            if (orderBy == "")
                orderBy = "PartnerProductId";

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@orderby", SqlDbType.VarChar, 50, ParameterDirection.Input, orderBy);
            AddParameterToSqlCmd(sqlCmd, "@startrow", SqlDbType.Int, 0, ParameterDirection.Input, startRowIndex);
            AddParameterToSqlCmd(sqlCmd, "@pagesize", SqlDbType.Int, 0, ParameterDirection.Input, maximumRows);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_TBLPARTNERPRODUCT_GETALLTBLPARTNERPRODUCTPAGED);

            GenerateCollectionFromReader test = new GenerateCollectionFromReader(GenerateTblPartnerProductCollectionFromReader);
            List<ManufacturerProduct> objCollection = ((List<ManufacturerProduct>)ExecuteReaderCmd(sqlCmd, test));
            return objCollection;
        }

        public static int GetAllTblPartnerProductPagedCount(String orderBy, Int32 startRowIndex, Int32 maximumRows)
        {
            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();

            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_TBLPARTNERPRODUCT_GETALLTBLPARTNERPRODUCTPAGEDCOUNT);
            ExecuteScalarCmd(sqlCmd);

            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return returnValue;
        }

        public static bool Save(ManufacturerProduct productPartner)
        {
            if (productPartner.PartnerProductId <= GetPartnerProductIdMinValue)
            {
                Decimal TempId = CreateNewTblPartnerProduct(productPartner);
                if (TempId != null)
                {
                    productPartner.PartnerProductId = TempId;
                    return true;
                }
                else
                {
                    // error occurs
                    return false;
                }
            }
            else
            {	//This is a update Command
                return (UpdateTblPartnerProduct(productPartner));
            }

        }

        public static Decimal CreateNewTblPartnerProduct(ManufacturerProduct newTblPartnerProduct)
        {
            // validate Parameters
            if (newTblPartnerProduct == null)
                throw (new ArgumentNullException("newTblPartnerProduct"));

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            int length = 0;
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);

            AddParameterToSqlCmd(sqlCmd, "@PartnerId", SqlDbType.Int, 0, ParameterDirection.Input, newTblPartnerProduct.PartnerId);
            AddParameterToSqlCmd(sqlCmd, "@ProductId", SqlDbType.Int, 0, ParameterDirection.Input, newTblPartnerProduct.ProductId);
            AddParameterToSqlCmd(sqlCmd, "@SellPrice", SqlDbType.Decimal, 9, ParameterDirection.Input, newTblPartnerProduct.SellPrice);
            AddParameterToSqlCmd(sqlCmd, "@ProductionCost", SqlDbType.Decimal, 9, ParameterDirection.Input, newTblPartnerProduct.ProductionCost);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_TBLPARTNERPRODUCT_CREATENEWTBLPARTNERPRODUCT);
            ExecuteScalarCmd(sqlCmd);
            return (Decimal.Parse(sqlCmd.Parameters["@ReturnValue"].Value.ToString()));

        }

        public static bool UpdateTblPartnerProduct(ManufacturerProduct newTblPartnerProduct)
        {
            // validate Parameters
            if (newTblPartnerProduct == null)
                throw (new ArgumentNullException("newTblPartnerProduct"));

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            int length = 0;
            AddParameterToSqlCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);

            AddParameterToSqlCmd(sqlCmd, "@PartnerProductId", SqlDbType.Decimal, 9, ParameterDirection.Input, newTblPartnerProduct.PartnerProductId);
            AddParameterToSqlCmd(sqlCmd, "@PartnerId", SqlDbType.Int, 0, ParameterDirection.Input, newTblPartnerProduct.PartnerId);
            AddParameterToSqlCmd(sqlCmd, "@ProductId", SqlDbType.Int, 0, ParameterDirection.Input, newTblPartnerProduct.ProductId);
            AddParameterToSqlCmd(sqlCmd, "@SellPrice", SqlDbType.Decimal, 9, ParameterDirection.Input, newTblPartnerProduct.SellPrice);
            AddParameterToSqlCmd(sqlCmd, "@ProductionCost", SqlDbType.Decimal, 9, ParameterDirection.Input, newTblPartnerProduct.ProductionCost);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SPR_TBLPARTNERPRODUCT_UPDATETBLPARTNERPRODUCT);
            ExecuteScalarCmd(sqlCmd);
            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return (returnValue == 0 ? false : true);
        }

        public static IList GenerateTblPartnerProductCollectionFromReader(IDataReader returnData)
        {
            List<ManufacturerProduct> colTblPartnerProduct = new List<ManufacturerProduct>();

            while (returnData.Read())
            {
                //passing the TblPartnerProduct constructor parameters from the current instance of data reader fields.
                ManufacturerProduct newTblPartnerProduct = new ManufacturerProduct()
                {
                    PartnerProductId = returnData["PartnerProductId"] == System.DBNull.Value ? GetPartnerProductIdMinValue : (Decimal)returnData["PartnerProductId"],
                    PartnerId = returnData["PartnerId"] == System.DBNull.Value ? 0 : (Int32)returnData["PartnerId"],
                    ProductId = returnData["ProductId"] == System.DBNull.Value ? 0 : (Int32)returnData["ProductId"],
                    SellPrice = returnData["SellPrice"] == System.DBNull.Value ? 0 : (Decimal)returnData["SellPrice"],
                    ProductionCost = returnData["ProductionCost"] == System.DBNull.Value ? 0 : (Decimal)returnData["ProductionCost"]

                };
                //adding the Employee to the collection
                colTblPartnerProduct.Add(newTblPartnerProduct);
            }

            //returns the collection of Companys 
            return (colTblPartnerProduct);
        }

        public static Decimal GetPartnerProductIdMinValue
        {
            get
            {
                return 0;
            }
        }

        //create method related contants
        internal const string SPR_TBLPARTNERPRODUCT_CREATENEWTBLPARTNERPRODUCT = "spr_TblPartnerProduct_CreateNewTblPartnerProduct";
        internal const string SPR_TBLPARTNERPRODUCT_UPDATETBLPARTNERPRODUCT = "spr_TblPartnerProduct_UpdateTblPartnerProduct";
        internal const string SPR_TBLPARTNERPRODUCT_DELETETBLPARTNERPRODUCT = "spr_TblPartnerProduct_DeleteTblPartnerProduct";
        internal const string SPR_TBLPARTNERPRODUCT_DELETETBLPARTNERPRODUCTLIST = "spr_TblPartnerProduct_DeleteTblPartnerProductList";
        internal const string SPR_TBLPARTNERPRODUCT_GETTBLPARTNERPRODUCTBYPARTNERPRODUCTID = "spr_TblPartnerProduct_GetTblPartnerProductByPartnerProductId";
        internal const string SPR_TBLPARTNERPRODUCT_GETALLTBLPARTNERPRODUCT = "spr_TblPartnerProduct_GetAllTblPartnerProduct";
        internal const string SPR_TBLPARTNERPRODUCT_GETALLTBLPARTNERPRODUCTPAGED = "spr_TblPartnerProduct_GetAllTblPartnerProductPaged";
        internal const string SPR_TBLPARTNERPRODUCT_GETALLTBLPARTNERPRODUCTPAGEDCOUNT = "spr_TblPartnerProduct_GetAllTblPartnerProductPagedCount";

        public static List<Product> GetAllTblPartnerProductByPartnerName(String partnerName, String categoryName)
        {
            if (categoryName.EndsWith("/"))
                categoryName = "~/" + categoryName;
            else
                categoryName = "~/" + categoryName + "/";

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@partnerName", SqlDbType.VarChar, partnerName.Length, ParameterDirection.Input, partnerName);
            AddParameterToSqlCmd(sqlCmd, "@catName", SqlDbType.VarChar, categoryName.Length, ParameterDirection.Input, categoryName);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, "spr_TblPartnerProduct_GetTblPartnerProductByPartnerCategory");

            GenerateCollectionFromReader test = new GenerateCollectionFromReader(GenerateTblProductCollectionFromReaderExtended);
            List<Product> objCollection = ((List<Product>)ExecuteReaderCmd(sqlCmd, test));
            return objCollection;
        }

        public static List<Product> GetAllTblPartnerProductByPartnerName(String partnerName)
        {

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@partnerName", SqlDbType.VarChar, partnerName.Length, ParameterDirection.Input, partnerName);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, "spr_TblPartnerProduct_GetTblPartnerProductByPartnerName");

            GenerateCollectionFromReader test = new GenerateCollectionFromReader(GenerateTblProductCollectionFromReaderExtended);
            List<Product> objCollection = ((List<Product>)ExecuteReaderCmd(sqlCmd, test));
            return objCollection;
        }

        public static List<Product> GetAllFeaturedProductByPartnerName(String partnerName)
        {

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@partnerName", SqlDbType.VarChar, partnerName.Length, ParameterDirection.Input, partnerName);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, "spr_TblPartnerProduct_GetAllFeaturedProductByPartnerName");

            GenerateCollectionFromReader test = new GenerateCollectionFromReader(GenerateTblProductCollectionFromReaderExtended);
            List<Product> objCollection = ((List<Product>)ExecuteReaderCmd(sqlCmd, test));
            return objCollection;
        }

        public static List<ManufacturerProduct> GetAllTblPartnerProductByProductId(Int32 prodId)
        {

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@productId", SqlDbType.Int, 0, ParameterDirection.Input, prodId);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, "spr_TblPartnerProduct_GetAllTblPartnerProductByProductId");

            GenerateCollectionFromReader test = new GenerateCollectionFromReader(GenerateProductPartnerCollectionFromReaderExtended);
            List<ManufacturerProduct> objCollection = ((List<ManufacturerProduct>)ExecuteReaderCmd(sqlCmd, test));
            return objCollection;
        }

        public static Product GetTblPartnerProductByPartnerName(String partnerName, Int32 prodId)
        {
            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParameterToSqlCmd(sqlCmd, "@partnerName", SqlDbType.VarChar, partnerName.Length, ParameterDirection.Input, partnerName);
            AddParameterToSqlCmd(sqlCmd, "@prodId", SqlDbType.Int, partnerName.Length, ParameterDirection.Input, prodId);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, "spr_TblPartnerProduct_GetTblPartnerProductByProductId");

            GenerateCollectionFromReader test = new GenerateCollectionFromReader(GenerateTblProductCollectionFromReaderExtended);
            List<Product> objCollection = ((List<Product>)ExecuteReaderCmd(sqlCmd, test));
            if (objCollection != null && objCollection.Count > 0)
            {
                return objCollection[0];
            }
            else
            {
                return null;
            }
        }


        public static IList GenerateTblProductCollectionFromReaderExtended(IDataReader returnData)
        {

            List<Product> colTblProduct = new List<Product>();


            while (returnData.Read())
            {
                //passing the TblProduct constructor parameters from the current instance of data reader fields.
                Product newTblProduct = new Product()
                {
                    ProductId = returnData["ProductId"] == System.DBNull.Value ? 0 : (Int32)returnData["ProductId"],
                    ProductName = returnData["ProductName"] == System.DBNull.Value ? string.Empty : (String)returnData["ProductName"],
                    ProductDescription = returnData["ProductDescription"] == System.DBNull.Value ? null : (String)returnData["ProductDescription"],
                    ProductImageUrl = returnData["ProductImageUrl"] == System.DBNull.Value ? string.Empty : (String)returnData["ProductImageUrl"],
                    DisplayOrder = returnData["DisplayOrder"] == System.DBNull.Value ? string.Empty : (String)returnData["DisplayOrder"],
                    ProductUrl = returnData["ProductUrl"] == System.DBNull.Value ? string.Empty : (String)returnData["ProductUrl"],
                    ProductPrice = (double)(returnData["SellPrice"] == System.DBNull.Value ? 0 : (Decimal)returnData["SellPrice"]),
                    RetailPrice = (double)(returnData["RetailPrice"] == System.DBNull.Value ? 0 : (Decimal)returnData["RetailPrice"]),
                    Specification = returnData["AdditionalInfo"] == System.DBNull.Value ? string.Empty : (String)returnData["AdditionalInfo"],
                    ItemNumber = returnData["ItemNumber"] == System.DBNull.Value ? string.Empty : (String)returnData["ItemNumber"],
                    IsFeatured = returnData["IsFeatured"] == System.DBNull.Value ? false : (Boolean)returnData["IsFeatured"],
                    IsNewArrival = returnData["IsNewArrival"] == System.DBNull.Value ? false : (Boolean)returnData["IsNewArrival"],
                    IsActive = returnData["IsActive"] == System.DBNull.Value ? false : (Boolean)returnData["IsActive"],
                    IsClearenceItem = returnData["IsClearenceItem"] == System.DBNull.Value ? false : (Boolean)returnData["IsClearenceItem"],
                    BrandId = returnData["BrandId"] == System.DBNull.Value ? 0 : (Int32)returnData["BrandId"],
                    CategoryId = returnData["CategoryId"] == System.DBNull.Value ? 0 : (Int32)returnData["CategoryId"],
                };
                //adding the Employee to the collection
                colTblProduct.Add(newTblProduct);
            }

            //returns the collection of Companys 
            return (colTblProduct);
        }

        public static IList GenerateProductPartnerCollectionFromReaderExtended(IDataReader returnData)
        {

            List<ManufacturerProduct> colTblProduct = new List<ManufacturerProduct>();

            while (returnData.Read())
            {
                ManufacturerProduct brand = new ManufacturerProduct();
                brand.BrandName = (String)returnData["BrandName"];
                brand.PartnerId = (Int32)returnData["PartnerId"];
                brand.PartnerProductId = (Decimal)returnData["PartnerProductId"];
                brand.ProductId = (Int32)returnData["ProductId"];
                brand.ProductionCost = (Decimal)returnData["ProductionCost"];
                brand.SellPrice = (Decimal)returnData["SellPrice"];
                colTblProduct.Add(brand);
            }

            //returns the collection of Companys 
            return (colTblProduct);
        }
    }
}
